﻿
using Dapper;
using HBase;
using hCommand;

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace Persistence.abs
{
    public abstract class AbsItemServer : AbsWMSBaseServer, IItemServer
    {

        //物料添加
        public long AddMaterial(CoreItem coreItem)
        {

            coreItem.classifyId = coreItem.classifyId;
            coreItem.active = 1;

            return opConn().Insert<CoreItem>(coreItem);
        }
        public Page<CoreItem> getIndexItemPage(PageDto dto)
        {

            string sql = @"SELECT *
                           FROM  core_item ci
                           where    active=1  and limit_max>0 and limit_min>=0  and (count>limit_max or count<limit_min)";

            return this.queryPage<CoreItem>(sql, "order_no", dto);
        }
        //分页查询
        public Page<CoreItem> QueryMaterialPage<CoreItem>(CoreItemDto dto)
        {
            string sql = @"SELECT *
                           FROM  core_item ci
                           where    1=1 ";
            if (dto != null && !string.IsNullOrEmpty(dto.name))
            {
                dto.name = "%" + dto.name + "%";
                sql += " AND ci.name like @name ";
            }
            if (dto != null && !string.IsNullOrEmpty(dto.code))
            {
                dto.code = "%" + dto.code + "%";
                sql += " AND ci.code like @code ";
            }
            if (dto != null && dto.classifyId != 0)
            {
                sql += " AND ci.classify_Id = @classifyId ";
            }

            if (dto != null && !string.IsNullOrEmpty(dto.modelSpecs))
            {
                dto.modelSpecs = "%" + dto.modelSpecs + "%";
                sql += " AND ci.model_specs like @modelSpecs ";
            }
            if (dto != null && !string.IsNullOrEmpty(dto.material))
            {
                dto.material = "%" + dto.material + "%";
                sql += " AND ci.material like @material ";
            }
            if (dto != null && !string.IsNullOrEmpty(dto.color))
            {
                dto.color = "%" + dto.color + "%";
                sql += " AND ci.color like @color ";
            }
            if (dto != null && dto.thickness != null)
            {
                dto.thickness = dto.thickness;
                sql += " AND ci.thickness = @thickness ";
            }
            return this.queryPage<CoreItem>(sql, "order_no", dto);
        }

        public List<CoreItemVo> getAllItem()
        {
            String sql = "select name n,gg g,code c,id i,unit u from  core_item  where active=1 order by order_no ";
            return readConn().Query<CoreItemVo>(sql).ToList();
        }
        //修改激活状态
        public bool UpdateActive(CoreItem model)
        {
            opConn().Update<CoreItem>(model);
            return true;
        }

        public bool UpdateInfo(CoreItem model)
        {
            string sql = @"UPDATE core_item SET classify_Id =@classifyId ,name=@name,gg=@gg
                            ,order_no=@orderNo,fenlei=@fenlei,unit=@unit,limit_Max=@limitMax,limit_Min=@limitMin WHERE id=@id";
            int a = opConn().Execute(sql, model);
            if (a >= 1)
            {
                return true;
            }
            return false;
        }
        //通过物料类别ID查询信息
        public List<CoreItem> GetCoreItemByClassfiyId(long classifyId)
        {
            String sql = "select * from  core_item where classify_Id=@classifyId and   active=1  order by order_no ";
            return readConn().Query<CoreItem>(sql, new { classifyId = classifyId }).ToList();
        }
        //物料删除
        public int DeleteCoreItem(List<CoreItem> coreItemList)
        {
            foreach (var coreItem in coreItemList)
            {
                opConn().Delete<CoreItem>("id=" + coreItem.id.ToString());
            }
            return 1;
        }
        //通过物料code查询信息
        public List<CoreItem> GetCoreItemByCode(string code)
        {
            String sql = "select * from core_item where code=@code order by order_no ";
            return readConn().Query<CoreItem>(sql, new { code = code }).ToList();
        }
        //通过Id找物料
        public CoreItem FindCoreItemById(long id)
        {
            return readConn().Get<CoreItem>(id);
        }

        public List<CoreItemDto> GetExportList(CoreItemDto dto)
        {
            string sql = @"SELECT A.* FROM  core_item A ";
            if (dto != null && !string.IsNullOrEmpty(dto.name))
            {
                dto.name = "%" + dto.name + "%";
                sql += " AND A.name like @name ";
            }
            if (dto != null && dto.classifyId != 0)
            {
                sql += " AND A.classify_Id = @classifyId ";
            }

            sql += " order by order_no ";


            return readConn().Query<CoreItemDto>(sql, dto).ToList();

        }


        #region 物料类别
        //添加
        public long AddClassify(CoreClassify model)
        {

            return opConn().Insert<CoreClassify>(model);
        }
        //修改
        public bool UpdateClassify(CoreClassify model)
        {
            //model.lastUpdateBy = model.name;
            //model.lastUpdateTime = sequenceIdServer.GetTime();
            //UpdateClassifyId()
            return opConn().Update<CoreClassify>(model);
        }

        //修改库存物料分类
        public int UpdateStockClassify()
        {
            string sql = @"UPDATE core_stock s JOIN core_item i SET s.classify_Id = i.classify_Id WHERE s.d_Item_id=i.id AND s.status>0 ";
            return opConn().Execute(sql);
        }
        //删除
        public bool DeleteByClassifyId(long classifyId)
        {
            return opConn().Delete<CoreItem>("user_ids=" + classifyId);
        }
        //删除物料类别
        public int DeleteClassify(List<CoreClassify> classifyList)
        {
            foreach (var classify in classifyList)
            {
                opConn().Delete<CoreClassify>("id=" + classify.id.ToString());
            }
            return 1;
        }
        //通过ID查找
        public CoreClassify FindClassifyById(long id)
        {
            return readConn().Get<CoreClassify>(id);
        }
        //分页显示
        public Page<ClassQuery> QueryClassifyPage<ClassQuery>(ClassifyDto dto)
        {
            string sql = @"SELECT cc.* FROM  Core_classify cc where 1=1";
            if (dto != null && !string.IsNullOrEmpty(dto.name))
            {
                dto.name = "%" + dto.name + "%";
                sql += " AND cc.name like @name ";
            }
            return this.queryPage<ClassQuery>(sql, "id", dto);
        }
        //根据名称查询
        public bool QueryClassifyPageByName(CoreClassify coreClassify)
        {
            string sql = @"SELECT * FROM  Core_classify cc where cc.name='{0}'";
            sql = string.Format(sql, coreClassify.name);
            List<CoreClassify> list = readConn().Query<CoreClassify>(sql, coreClassify).ToList();
            if (list.Count == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
            //return this.queryPage<ClassQuery>(sql, "id", dto);
        }

        //根据名称查询
        public CoreClassify QueryClassifyByName(string classifyName)
        {
            string sql = @"SELECT * FROM  Core_classify cc where cc.name='{0}'";
            sql = string.Format(sql, classifyName);
            List<CoreClassify> list = readConn().Query<CoreClassify>(sql, classifyName).ToList();
            CoreClassify coreClassify = list[0];
            //return this.queryPage<ClassQuery>(sql, "id", dto);
            return coreClassify;
        }


        //批量设置物料类别库位信息

        public bool BatchSetLoc(List<long> locList, long areaid, int eigen)
        {
            CoreClassifyArea coreClassifyArea = new CoreClassifyArea();
            for (int i = 0; i < locList.Count(); i++)
            {
                long iid = locList[i];

                coreClassifyArea.classifyId = iid;
                coreClassifyArea.areaId = areaid;
                coreClassifyArea.eigen = eigen;
                insertNotNull(coreClassifyArea);
            }
            return true;
        }

        public bool ImportList(List<CoreItem> coreItemList, out string message)
        {
            if (!Check(coreItemList, out message))
            {
                return false;
            }
            List<CoreItem> coreItems = new List<CoreItem>();
            foreach (var model in coreItemList)
            {
                model.gg = model.gg == null ? "" : model.gg;
                CoreItem coreItem = readConn().Query<CoreItem>("select * from core_item where code=@code", new { code = model.code }).FirstOrDefault();

                if (coreItem == null)
                {
                    if (coreItems.Where(x => x.code == model.code).FirstOrDefault() != null)
                    {
                        continue;
                    }
                    coreItems.Add(model);
                }
                 
                model.id = WMSIdUtil.getId();
            }
            try
            {
                if (coreItems.Count <=0)
                {
                    message = "操作成功";
                    return true;
                }
                //string sql = @"INSERT INTO  core_item (id ,classify_Id ,code ,name ,active ,core_Item_Type ,model_specs ,package_specs)
                //            VALUES  ( @id ,@classifyId , @code ,@name , @active, @coreItemType,@modelSpecs ,@packageSpecs)";
                if (opConn().BatchInsert(coreItems) > 0)
                {
                    message = "操作成功";
                    return true;
                }
                else
                {
                    message = "Excel数据异常";
                    return false;
                }
            }
            catch (Exception ex)
            {

                message = ex.Message;
                return false;
            }

        }

        private bool Check(List<CoreItem> coreItemList, out string message)
        {
            int oldCount = coreItemList.Count;
            message = string.Empty;
            if (coreItemList.Count == 0)
            {
                message = "当前Excel不存在数据!";
                return false;
            }
            //if (coreItemList.Exists(a => a.coreItemType == 0))
            //{
            //    message = "Excel里面的物料类型不存在";
            //    return false;

            //}
            if (coreItemList.Exists(a => a.classifyId == 0))
            {
                message = "Excel里面的物料类别不存在";
                return false;

            }
            //int codeCount =coreItemList.Select(p => p.code).ToList().Distinct().ToList().Count;
            //if( oldCount != codeCount ) {
            //    message = "Excel里面编码存在重复";
            //    return false;
            //}
            //var newList = from m in coreItemList select new { m.name };
            //int nameCount = newList.ToList().Distinct().ToList().Count;
            //if( oldCount != nameCount ) {
            //    message = "Excel里面物料规格存在重复";
            //    return false;
            //}
            return true;
        }

        #endregion


        #region 物料类别分区
        //添加
        public long AddClassifyArea(CoreClassifyArea model)
        {
            return opConn().Insert<CoreClassifyArea>(model);
        }
        //修改
        public bool UpdateClassifyArea(CoreClassifyArea model)
        {
            return opConn().Update<CoreClassifyArea>(model);
        }

        //删除物料类别
        public int DeleteClassifyArea(List<CoreClassifyArea> classifyAreaList)
        {
            foreach (var classifyArea in classifyAreaList)
            {
                opConn().Delete<CoreClassifyArea>("id=" + classifyArea.id.ToString());
            }
            return 1;
        }

        //分页显示
        public Page<CoreClassifyArea> QueryClassifyAreaPage(CoreClassifyAreaDto dto)
        {
            string sql = @"select * from core_classify_Area where 1=1";
            if (dto!=null) {
                if (dto.classifyId!=0) {
                    sql+=" AND classify_Id=@classifyId ";
                }
                if (dto.areaId!=0) {
                    sql+=" AND area_Id=@areaId ";
                }
                if (dto.priority!=0) {
                    sql+=" AND priority=@priority ";
                }
            }
           
            return this.queryPage<CoreClassifyArea>(sql, "id", dto);
        }



        #endregion
    }
}
